﻿Imports sstools.GenericExcelExporter

Module mdlMain
    Sub Main()
        Dim aw As awDataContext
        Dim sqlServer, dbName As String
        Dim keyboard As New Microsoft.VisualBasic.Devices.Keyboard
        Do
            Try
                Console.WriteLine("Enter SQL Server Address:")
                keyboard.SendKeys("localhost", True)
                sqlServer = Console.ReadLine

                Console.WriteLine("Enter Database Name:")
                keyboard.SendKeys("AdventureWorks", True)
                dbName = Console.ReadLine

                Console.WriteLine("(connecting...)")
                aw = New awDataContext("Data Source=" & sqlServer & ";Initial Catalog=" & dbName & ";Integrated Security=True")
                aw.Connection.Open()
                Exit Do
            Catch ex As Exception
                Console.WriteLine("Error occured: " & ex.Message)
                Console.WriteLine("")
            End Try
        Loop

        Do
            Console.WriteLine("")
            Console.WriteLine("Select example output (1-3)")
            Console.WriteLine("1 Metadatatype Approach")
            Console.WriteLine("2 MapEntities Approach")
            Console.WriteLine("3 MapEntities Approach Extended")
            Console.WriteLine("")
            Console.WriteLine("(press enter for exit)")

            Dim tmr As Long = Now.Ticks
            Dim recordCount As Integer = 0
            Dim tmpFile As String = IO.Path.Combine(IO.Path.GetTempPath, IO.Path.GetTempFileName.Replace(".tmp", ".xlsx"))

            Select Case Console.ReadLine
                Case "1"
                    Console.WriteLine("(exporting...)")
                    recordCount = MetadatatypeApr(aw, tmpFile)
                Case "2"
                    Console.WriteLine("(exporting...)")
                    recordCount = MapEntitiesApr(aw, tmpFile)
                Case "3"
                    Console.WriteLine("(exporting...)")
                    recordCount = MapEntitiesAprExtended(aw, tmpFile)
                Case ""
                    Exit Do
                Case Else
                    Console.WriteLine("Inappropriate choice!")
                    Continue Do
            End Select

            Dim elapsed As Long = Fix((Now.Ticks - tmr) / TimeSpan.TicksPerMillisecond)
            Console.WriteLine("Exporting to Excel finished - " & tmpFile)
            Console.WriteLine("Total elapsed: " & elapsed & "ms / Elapsed per entity: " & Fix(elapsed / recordCount) & "ms")
            Process.Start(tmpFile)
        Loop
    End Sub

    Function MetadatatypeApr(aw As awDataContext, tmpFile As String) As Integer
        Dim contacts = (From c As Contact In aw.Contacts Take (100) Order By c.LastName, c.FirstName, c.ContactID Select c).ToList
        contacts.ExportToExcel(tmpFile)
        Return contacts.Count
    End Function

    Function MapEntitiesApr(aw As awDataContext, tmpFile As String) As Integer
        Dim customContacts = (From c In aw.Contacts Take 100 Select New CustomContact With {.ContactID = c.ContactID,
                                                                               .EmailAddress = c.EmailAddress,
                                                                               .FirstName = c.FirstName,
                                                                               .LastName = c.LastName,
                                                                               .ModifiedDate = c.ModifiedDate}).ToList
        customContacts.ExportToExcel(tmpFile)
        Return customContacts.Count
    End Function

    Function MapEntitiesAprExtended(aw As awDataContext, tmpFile As String) As Integer
        Dim customEmployees = (From c In aw.Contacts
                       Where c.Employees.Count > 0
                       Take 100
                       Let em = c.Employees.FirstOrDefault
                       Let ad = em.EmployeeAddresses.FirstOrDefault.Address
                       Let st = ad.StateProvince
                       Let tt = st.SalesTerritory
                       Select New CustomEmployee With {.ContactID = c.ContactID,
                                                       .EmailAddress = c.EmailAddress,
                                                       .FirstName = c.FirstName,
                                                       .LastName = c.LastName,
                                                       .ModifiedDate = c.ModifiedDate,
                                                       .Address = New CustomAddress With {.City = ad.City,
                                                                                          .State = New CustomStateProvince With {.CountryCode = st.CountryRegionCode,
                                                                                                                                 .Name = st.Name,
                                                                                                                                 .Territory = New CustomTerritory With {.Name = tt.Name,
                                                                                                                                                                        .Group = tt.Group,
                                                                                                                                                                        .SalesLastYear = tt.SalesLastYear,
                                                                                                                                                                        .CostLastYear = tt.CostLastYear
                                                                                                                                                                       }
                                                                                                                                }
                                                                                         }
                                                      }).ToList

        customEmployees.ExportToExcel(tmpFile)
        Return customEmployees.Count
    End Function

End Module
